package cn.com.libertymutual.sp.dao;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpBank;

/**
 * @author AoYi
 * 注意@Modifying注解需要使用clearAutomatically=true;
 * 同一接口更新后立即查询获得更新后的数据,默认false查询还是更新前的数据
 */
@Repository
public interface BankDao extends PagingAndSortingRepository<TbSpBank, Integer>, JpaSpecificationExecutor<TbSpBank> {
	// 修改某用户的银行卡状态
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpBank set updateTime = :updateTime,unbundingTime = :unbundingTime, state = :state where state=1 and userCode = :userCode and number = :number")
	public int updateStateByUserCode(@Param("updateTime") Date updateTime, @Param("unbundingTime") Date unbundingTime, @Param("state") Integer state,
			@Param("userCode") String userCode, @Param("number") String number);

	// 修改某用户的银行卡状态
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpBank set updateTime = :updateTime,unbundingTime = :unbundingTime, state = :state where userCode = :userCode")
	public int updateStateByUserCode(@Param("updateTime") Date updateTime, @Param("unbundingTime") Date unbundingTime, @Param("state") Integer state,
			@Param("userCode") String userCode);

	// 修改用户编码
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpBank set updateTime = :updateTime, userCode = :userCode1 where userCode = :userCode2")
	public int updateUserCodeByUserCode(@Param("updateTime") Date updateTime, @Param("userCode1") String userCode1,
			@Param("userCode2") String userCode2);

	// 设置某用户银行卡为默认
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpBank set updateTime = :updateTime, isDefault = 1 where isDefault=0 and id = :id")
	public int updateDefaultById(@Param("updateTime") Date updateTime, @Param("id") Integer id);

	// 设置某用户非默认银行卡状态
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpBank set updateTime = :updateTime, isDefault = 0 where userCode = :userCode and id <> :id")
	public int updateNotDefaultById(@Param("updateTime") Date updateTime, @Param("userCode") String userCode, @Param("id") Integer id);

	// 根据卡号查询
	@Query("select t from TbSpBank t where state=1 and number = :number")
	public List<TbSpBank> findByNumber(@Param("number") String number);

	// 查询某用户是否存在该卡号
	@Query("select count(id) from TbSpBank t where state=1 and userCode = :userCode and number = :number")
	public int findByNumberAndUserCode(@Param("userCode") String userCode, @Param("number") String number);

	// 查询某用户是否解绑该卡号,使用limit取出从下标为0的
	@Query(value = "select * from tb_sp_bank where state=0 and user_code = :user_code and number = :number limit 0,1", nativeQuery = true)
	public TbSpBank findByNumberOfUnbundling(@Param("user_code") String user_code, @Param("number") String number);

	// 查询卡号是否被其他账户绑定
	@Query(value = "select count(id) from tb_sp_bank where user_code <> :user_code and number = :number", nativeQuery = true)
	public int findByNumberOfOtherUser(@Param("user_code") String user_code, @Param("number") String number);

	// 查询某用户全部银行卡
	@Query("select t from TbSpBank t where state=1 and (userCode = :userCode or userCode = :userCodeBs) order by isDefault desc,createTime desc")
	public List<TbSpBank> findAllByUserCode(@Param("userCode") String userCode, @Param("userCodeBs") String userCodeBs);

	// 查询张银行卡
	@Query("select t from TbSpBank t where state=1 and id = :id")
	public TbSpBank findBankById(@Param("id") Integer id);

	// 查询某用户的默认银行卡
	@Query("select t from TbSpBank t where state=1 and isDefault=1 and (userCode = :userCode or userCode = :userCodeBs)")
	public List<TbSpBank> findDefaultBankByUserCode(@Param("userCode") String userCode, @Param("userCodeBs") String userCodeBs);

	// 查询某用户是否存在默认银行卡
	@Query("select count(id) from TbSpBank t where state=1 and isDefault=1 and (userCode = :userCode or userCode = :userCodeBs)")
	public int findDefaultByUserCode(@Param("userCode") String userCode, @Param("userCodeBs") String userCodeBs);

}
